--    Author    : MAYANTING
--    Name      : ADM.RPT_SUST_DLMG_PAST_ECON_LOAF.HQL
--    Functions : 表25：贷款发生额分经济成分统计表
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2018-06-11  MAYANTING           1.CREATE THE PROCEDURE
--
INSERT OVERWRITE TABLE ADM.RPT_SUST_DLMG_PAST_ECON_LOAF PARTITION (DATA_DATE = '20180331')
SELECT  AREA.AREA_CODE_4                 AS FIN_ORG_DIST
       ,T.CCY                            AS CCY
       ,T.ECONOMY_TYPE                   AS ECONOMY_TYPE
       ,T.TYPE_DESC                      AS TYPE_DESC
       ,SUM(T.OCCUR_AMOUNT)/100000000    AS OCCUR_AMOUNT       -- 贷款发生额
       ,SUM(T.AMOUNT_LM    )/100000000   AS AMOUNT_LM          -- 发生额上期末
       ,SUM(T.AMOUNT_YS    )/100000000   AS AMOUNT_YS          -- 当年累计发生额
       ,SUM(T.AMOUNT_GROW  )/100000000   AS AMOUNT_GROW        -- 同比增长
       ,SUM(T.WSUM_AMOUNT  )/100000000   AS WSUM_AMOUNT        -- 发生额加权值
       ,SUM(T.WAMOUNT_LM   )/100000000   AS WAMOUNT_LM         -- 发生额加权值上期末
       ,SUM(T.WAMOUNT_YS   )/100000000   AS WAMOUNT_YS         -- 发生额加权值当年累计
       ,SUM(T.WAMOUNT_LC   )/100000000   AS WAMOUNT_LC         -- 发生额加权值去年同期
FROM (
    -- 企业出资人经济成分 一级
    SELECT
     FSE.FIN_ORG_NO                                                          AS FIN_ORG_NO
    ,FSE.CCY                                                                 AS CCY
    ,ECON.ECO_SEC_CAT_ID_1                                                   AS ECONOMY_TYPE     -- 企业出资人经济成分
    ,ECON.ECO_SEC_CAT_DSCR_1                                                 AS TYPE_DESC        -- 经济成分描述
    ,COALESCE(FSE.OCCUR_AMOUNT,0)                                            AS OCCUR_AMOUNT     -- 贷款发生额
    ,COALESCE(FSE.AMOUNT_LM,0)                                               AS AMOUNT_LM        -- 发生额上期末
    ,COALESCE(FSE.AMOUNT_YS,0)                                               AS AMOUNT_YS        -- 当年累计发生额
    ,COALESCE(FSE.OCCUR_AMOUNT,0) - COALESCE(FSE.AMOUNT_YC,0)                AS AMOUNT_GROW      -- 同比增减
    ,COALESCE(FSE.WSUM_AMOUNT,0)                                             AS WSUM_AMOUNT      -- 发生额加权值
    ,COALESCE(FSE.WAMOUNT_LM,0)                                              AS WAMOUNT_LM       -- 发生额加权值上期末
    ,COALESCE(FSE.WAMOUNT_YS,0)                                              AS WAMOUNT_YS       -- 发生额加权值当年累计
    ,COALESCE(FSE.WAMOUNT_YC,0)                                              AS WAMOUNT_LC       -- 发生额加权值去年同期
    FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE DATA_DATE='20180331') FSE
    -- 关联经济成分类别维度表
    LEFT JOIN DIMENSION.DIM_ECONOMIC_SECTOR_CATAGORY ECON ON FSE.ECONOMY_TYPE=ECON.ECO_SEC_CAT_ID_3 AND '20180331' BETWEEN ECON.START_DATE AND ECON.END_DATE

    UNION ALL

    -- 企业出资人经济成分 二级
    SELECT
     FSE.FIN_ORG_NO                                                          AS FIN_ORG_NO
    ,FSE.CCY                                                                 AS CCY
    ,ECON.ECO_SEC_CAT_ID_2                                                   AS ECONOMY_TYPE     -- 企业出资人经济成分
    ,ECON.ECO_SEC_CAT_DSCR_2                                                 AS TYPE_DESC        -- 经济成分描述
    ,COALESCE(FSE.OCCUR_AMOUNT,0)                                            AS OCCUR_AMOUNT     -- 贷款发生额
    ,COALESCE(FSE.AMOUNT_LM,0)                                               AS AMOUNT_LM        -- 发生额上期末
    ,COALESCE(FSE.AMOUNT_YS,0)                                               AS AMOUNT_YS        -- 当年累计发生额
    ,COALESCE(FSE.OCCUR_AMOUNT,0) - COALESCE(FSE.AMOUNT_YC,0)                AS AMOUNT_GROW      -- 同比增减
    ,COALESCE(FSE.WSUM_AMOUNT,0)                                             AS WSUM_AMOUNT      -- 发生额加权值
    ,COALESCE(FSE.WAMOUNT_LM,0)                                              AS WAMOUNT_LM       -- 发生额加权值上期末
    ,COALESCE(FSE.WAMOUNT_YS,0)                                              AS WAMOUNT_YS       -- 发生额加权值当年累计
    ,COALESCE(FSE.WAMOUNT_YC,0)                                              AS WAMOUNT_LC       -- 发生额加权值去年同期
    FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE DATA_DATE='20180331') FSE
    -- 关联经济成分类别维度表
    LEFT JOIN DIMENSION.DIM_ECONOMIC_SECTOR_CATAGORY ECON ON FSE.ECONOMY_TYPE=ECON.ECO_SEC_CAT_ID_3 AND '20180331' BETWEEN ECON.START_DATE AND ECON.END_DATE

    UNION ALL
    -- 合计
    SELECT
     FSE.FIN_ORG_NO                                                          AS FIN_ORG_NO
    ,FSE.CCY                                                                 AS CCY
    ,'AB'                                                                    AS ECONOMY_TYPE     -- 企业出资人经济成分
    ,'合计'                                                                  AS TYPE_DESC        -- 经济成分描述
    ,COALESCE(FSE.OCCUR_AMOUNT,0)                                            AS OCCUR_AMOUNT     -- 贷款发生额
    ,COALESCE(FSE.AMOUNT_LM,0)                                               AS AMOUNT_LM        -- 发生额上期末
    ,COALESCE(FSE.AMOUNT_YS,0)                                               AS AMOUNT_YS        -- 当年累计发生额
    ,COALESCE(FSE.OCCUR_AMOUNT,0) - COALESCE(FSE.AMOUNT_YC,0)                AS AMOUNT_GROW      -- 同比增减
    ,COALESCE(FSE.WSUM_AMOUNT,0)                                             AS WSUM_AMOUNT      -- 发生额加权值
    ,COALESCE(FSE.WAMOUNT_LM,0)                                              AS WAMOUNT_LM       -- 发生额加权值上期末
    ,COALESCE(FSE.WAMOUNT_YS,0)                                              AS WAMOUNT_YS       -- 发生额加权值当年累计
    ,COALESCE(FSE.WAMOUNT_YC,0)                                              AS WAMOUNT_LC       -- 发生额加权值去年同期
    FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE DATA_DATE='20180331') FSE
  )T
 -- 与地区表最细级关联
 LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON T.FIN_ORG_NO=REAL.ORG_ID AND '20180331' BETWEEN REAL.START_DATE AND REAL.END_DATE
 LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '20180331' BETWEEN AREA.START_DATE AND AREA.END_DATE
 GROUP BY  AREA.AREA_CODE_4
          ,T.CCY
          ,T.ECONOMY_TYPE
          ,T.TYPE_DESC
;